﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace MesBSW.Tools
{
    public static class ExcelTools
    {
        /// <summary>
        /// 读取EXCEL的所有页签（Sheet）
        /// </summary>
        /// <param name="fileName">Excel路径</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static List<string> ReadExcelSheetName(string fileName)
        {
            List<string> sheets = new List<string>();
            if (System.IO.File.Exists(fileName))
            {
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                IWorkbook wb = null;
                if (fileName.ToUpper().Contains(".XLSX"))
                {
                    wb = new XSSFWorkbook(fs); 
                } 
                else
                {
                    wb = new HSSFWorkbook(fs);
                }

                if (wb != null)
                {
                    int sheetIndex = wb.NumberOfSheets;
                    for (int i = 0;i < sheetIndex;i++)
                    {
                        sheets.Add(wb.GetSheetAt(i).SheetName);
                    }
                }
            }
            else
            {
                throw new Exception("文件不存在！");
            }

            return sheets;
        }


        public static System.Data.DataTable ReadExcelToDataTableNoColumn(string fileName, string sheetName)
        {
            DataTable resDt = new DataTable();
            if (System.IO.File.Exists(fileName))
            {
                System.IO.FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                IWorkbook wb = null;
                if (fileName.ToUpper().Contains(".XLSX"))
                {
                    wb = new XSSFWorkbook(fs);
                }
                else
                {
                    wb = new HSSFWorkbook(fs);
                }

                if (wb != null)
                {
                    ISheet sheet = wb.GetSheet(sheetName);
                    if (sheet != null)
                    {
                        int lastRowIndex = sheet.LastRowNum;
                        int lastCellIndex = 0;
                        for (int i = 0; i < lastRowIndex - 1; i++)
                        {
                            if (sheet.GetRow(i) != null)
                            {
                                int tmpInt = sheet.GetRow(i).LastCellNum;
                                if (tmpInt > lastCellIndex)
                                {
                                    lastCellIndex = tmpInt;
                                }
                            }
                        }

                        for (int i = 0; i < lastCellIndex; i++)
                        {
                            resDt.Columns.Add("Column" + String.Format("{0:D5}",i + 1));
                        }

                        for (int i = 0; i <= lastRowIndex; i++)
                        {
                            if (sheet.GetRow(i) != null)
                            {
                                DataRow bodyRow = resDt.NewRow();
                                for (int j = 0; j < lastCellIndex; j++)
                                {
                                    bodyRow[j] = ExcelTools.GetCellValue(sheet.GetRow(i).GetCell(j));
                                }
                                resDt.Rows.Add(bodyRow);
                            }
                        }
                    }
                }
            }
            else
            {
                throw new Exception("EXCEL文件不存在！");
            }
            return resDt;
        }


        /// <summary>
        /// 读取EXCEL表格，并将数据转成DataTable返回
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static System.Data.DataTable ReadExcelToDataTable(string fileName, string sheetName)
        {
            DataTable resDt = new DataTable();

            if (System.IO.File.Exists(fileName) == true)
            {
                System.IO.FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                IWorkbook wb = null;
                if (fileName.ToUpper().Contains(".XLSX"))
                {
                    wb = new XSSFWorkbook(fs);
                }
                else
                {
                    wb = new HSSFWorkbook(fs);
                }

                if (wb != null)
                {
                    ISheet sheet = wb.GetSheet(sheetName);
                    if (sheet != null)
                    {
                        int lastRowIndex = sheet.LastRowNum;
                        IRow titleRow = sheet.GetRow(0);
                        int lastCellIndex = titleRow.LastCellNum;

                        for (int i = 0; i < lastCellIndex ; i++)
                        {
                            resDt.Columns.Add(titleRow.GetCell(i).StringCellValue);
                        }

                        for (int i = 1; i <= lastRowIndex ; i++)
                        {
                            if (sheet.GetRow(i) != null)
                            {
                                DataRow bodyRow = resDt.NewRow();
                                for (int j = 0; j < lastCellIndex; j++)
                                {
                                    bodyRow[j] = ExcelTools.GetCellValue(sheet.GetRow(i).GetCell(j));
                                }
                                resDt.Rows.Add(bodyRow);
                            }
                        }
                    }
                }
            }
            else
            {
                throw new Exception("EXCEL文件不存在！");
            }

            if (resDt != null && resDt.Rows.Count > 0)
            {
                List<DataRow> delList = new List<DataRow>();
                foreach (DataRow row in resDt.Rows)
                {
                    Boolean isAllNull = true;
                    foreach (DataColumn clu in resDt.Columns)
                    {
                        if (row[clu.ColumnName].ToString().Trim().Equals("") == false)
                        {
                            isAllNull = false;
                            break;
                        }
                    }

                    if (isAllNull)
                    {
                        delList.Add(row);
                    }
                }

                if (delList != null && delList.Count > 0)
                {
                    foreach (DataRow row in delList)
                    {
                        resDt.Rows.Remove(row);
                    }
                }
            }
            return resDt;
        }


        /// <summary>
        /// 读取单元格的值
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public static object GetCellValue(ICell item)
        {
            if (item == null)
            {
                return "";
            }
            switch (item.CellType)
            {
                case CellType.Boolean:
                    return item.BooleanCellValue;

                case CellType.Error:
                    return ErrorEval.GetText(item.ErrorCellValue);

                case CellType.Formula:
                    switch (item.CachedFormulaResultType)
                    {
                        case CellType.Boolean:
                            return item.BooleanCellValue;

                        case CellType.Error:
                            return ErrorEval.GetText(item.ErrorCellValue);

                        case CellType.Numeric:
                            if (DateUtil.IsCellDateFormatted(item))
                            {
                                return item.DateCellValue.ToString("yyyy-MM-dd");
                            }
                            else
                            {
                                return item.NumericCellValue;
                            }
                        case CellType.String:
                            string str = item.StringCellValue;
                            if (!string.IsNullOrEmpty(str))
                            {
                                return str.ToString();
                            }
                            else
                            {
                                return "";
                            }
                        case CellType.Unknown:
                        case CellType.Blank:
                        default:
                            return "";
                    }
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(item))
                    {
                        return item.DateCellValue.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        return item.NumericCellValue;
                    }
                case CellType.String:
                    string strValue = item.StringCellValue;
                    return strValue.ToString().Trim();

                case CellType.Unknown:
                case CellType.Blank:
                default:
                    return "";
            }
        }

        
    }
}
